import mysql.connector

inst_info_sql_format = "INSERT INTO `jixint-product`.inst_info(id, code, name, abbr_name, inst_type, status) VALUES ({}, '{}', '{}', '{}', '3', 1);"
car_plate_sql_format = "INSERT INTO `jixint_product`.inst_field_val(inst_id, product_code, field_config_id, field_val) VALUES('{}', '{}', 1, '{}');"
term_sql_format = "INSERT INTO `jixint_product`.inst_field_val(inst_id, product_code, field_config_id, field_val) VALUES('{}', '{}', 2, '{}');"
min_rate_sql_format = "INSERT INTO `jixint_product`.inst_field_val(inst_id, product_code, field_config_id, field_val) VALUES('{}', '{}', 4, '{}');"
max_rate_sql_format = "INSERT INTO `jixint_product`.inst_field_val(inst_id, product_code, field_config_id, field_val) VALUES('{}', '{}', 3, '{}');"
up_sql_format = "INSERT INTO `jixint_product`.inst_field_val(inst_id, product_code, field_config_id, field_val) VALUES('{}', '{}', 5, '1000000000000');"
store_code_sql_format = "INSERT INTO `jixint_product`.inst_field_val(inst_id, product_code, field_config_id, field_val) VALUES('{}', '{}', 6, '{}');"


def generate_inst_info_sql(id, inst_code, inst_name):
    return inst_info_sql_format.format(id, inst_code, inst_name, inst_name)


def generate_car_plate_sql(id, product_code, province_code):
    mydb2 = mysql.connector.connect(
        host="172.20.100.12",
        port=3308,
        user="jixintdev",
        password="jixintgDev!",
        database="jixint-ucenter"
    )

    sql = []

    province_code_array = province_code.split(",")
    for province_code in province_code_array:
        select_sql = "SELECT * FROM t_district_info WHERE code LIKE '{}%'".format(province_code)

        my_cursor = mydb2.cursor()
        my_cursor.execute(select_sql)
        result = my_cursor.fetchall()
        for row in result:
            format_sql = car_plate_sql_format.format(id, product_code, row[1])
            sql.append(format_sql)

    return sql


def generate_term_sql(id, product_code, term):
    return term_sql_format.format(id, product_code, term)


def generate_min_rate_sql(id, product_code, min_rate):
    return min_rate_sql_format.format(id, product_code, min_rate)


def generate_max_rate_sql(id, product_code, max_rate):
    return max_rate_sql_format.format(id, product_code, max_rate)


def generate_up_sql(id, product_code):
    return up_sql_format.format(id, product_code)


def generate_store_code_sql(id, product_code, store_code):
    return store_code_sql_format.format(id, product_code, store_code)


def generate_all_sql(id, inst_code, inst_name, product_code, province, term, min_rate, max_rate, store_code):
    inst_info_sql = generate_inst_info_sql(id, inst_code, inst_name)
    car_sql_list = generate_car_plate_sql(id, product_code, province)
    term_sql = generate_term_sql(id, product_code, term)
    min_rate_sql = generate_min_rate_sql(id, product_code, min_rate)
    max_rate_sql = generate_max_rate_sql(id, product_code, max_rate)
    up_sql = generate_up_sql(id, product_code)
    store_code_sql = generate_store_code_sql(id, product_code, store_code)

    sql_name = inst_code + '_' + product_code + '.sql'

    # 这里将sql内容输出到文件
    with open(sql_name, 'w') as f:
        f.write(inst_info_sql + '\n')
        for car_sql in car_sql_list:
            f.write(car_sql + '\n')
        f.write(term_sql + '\n')
        f.write(min_rate_sql + '\n')
        f.write(max_rate_sql + '\n')
        f.write(up_sql + '\n')
        f.write(store_code_sql + '\n')


if __name__ == '__main__':
    id = input("请输入主键id：")
    inst_code = input("请输入机构编码：")
    inst_name = input("请输入机构名称：")
    product_code = input("请输入产品编码,eg:fm_jx：") or "fm_jx"
    province = input("请输入省份编码前两位，多个,隔开，eg:65,64：")
    term = input("请输入期数,eg:36：") or "36"
    min_rate = input("请输入最小利率,eg:23.96：") or "23.96"
    max_rate = input("请输入最大利率,eg:23.96：") or "23.96"
    store_code = input("请输入门店code：")

    generate_all_sql(id, inst_code, inst_name, product_code, province, term, min_rate, max_rate, store_code)
